<?php

namespace app\admin\controller\sta;

use app\admin\library\Auth;
use app\admin\model\Factory;
use app\admin\model\Pinxi;
use app\admin\model\Storea;
use app\admin\model\Storesub;
use app\common\controller\Backend;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use think\Db;
use think\db\exception\BindParamException;
use think\db\exception\DataNotFoundException;
use think\db\exception\ModelNotFoundException;
use think\exception\DbException;
use think\exception\PDOException;
use think\exception\ValidateException;
use think\response\Json;

/**
 * 生物资产收发存汇总表v2
 *
 * @icon fa fa-circle-o
 */
class Bios extends Backend
{

    /**
     * Bios模型对象
     * @var \app\admin\model\sta\Bios
     */
    protected $model = null;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\sta\Bios;
        $this->view->assign("statusList", $this->model->getStatusList());

        // demo  日期数组,用于快捷筛选条件
        $firstOfBios = Db::name("sta_bios")->order("createtime ASC")->select();
        $start = $firstOfBios[0] ? $firstOfBios[0]['createtime'] : time();

        $startMonth = date("Y-m", $start);
        $nextMonth = date('Y-m', strtotime('+1 month'));
        $monthList = createMonlist($startMonth, $nextMonth );

        $this->view->assign("monthList", $monthList);
        $this->assignconfig("monthList", $monthList);

        $this->assignconfig("factoryList", Factory::getList() );
        $this->assignconfig("storesubList", Storesub::getList() );
        $this->assignconfig("storeaList", Storea::getList() );
        $this->assignconfig("pinxiList", Pinxi::getList() );
    }

    /**
     * 查看
     *
     * @return string|Json
     * @throws \think\Exception
     * @throws DbException
     */
    public function index()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if (false === $this->request->isAjax()) {
            return $this->view->fetch();
        }
        //如果发送的来源是 Selectpage，则转发到 Selectpage
        if ($this->request->request('keyField')) {
            return $this->selectpage();
        }

        [$where, $sort, $order, $offset, $limit] = $this->buildparams();
        $list = $this->model
            ->where($where)
            ->order($sort, $order)
            ->paginate($limit);

        $list = $this->getXXX($list);

        $result = ['total' => $list->total(), 'rows' => $list->items()];
        return json($result);
    }

    // 按钮:  汇总x月的数据, 并保存到当月汇总数据
    // 引用:  如果系统时间, 大于汇总表 sta_bios 的时间, 则 1创建月初数据 2汇总上月数据,并标记已完成
    public function summonth__test(){
        $cal_month = "2022-05";
        $this->index_cal($cal_month);
    }

    /**
     * 统计
     *
     * @return string
     * @throws \think\Exception
     */
    public function summonth()
    {
        if (false === $this->request->isPost()) {
            return $this->view->fetch();
        }
        $params = $this->request->post('row/a');
        if (empty($params)) {
            $this->error(__('Parameter %s can not be empty', ''));
        }
        $params = $this->preExcludeFields($params);

        // todo 增加强制更新, 或者允许删除后, 重新生成

        // todo 如果存在上月数据, 或更早的数据, 结算之前的数据, 创建这个月的期初数据

        //  是否需要校验
        $cal_month = $params['createtime'];
        //  计算
        $result = $this->index_cal($cal_month);

        if ($result === false) {
            $this->error(__('汇总数据已存在,无法覆盖已存在的数据'));
        }
        if ($result['lines'] == 0) {
            $this->error(__('未获取到有效数据! '));
        }
        $this->success(__('汇总数据生成完毕.'). " 更新行数: ". json_encode($result['lines'])  );
    }
    // 计算x月的汇总数据    格式 "2022-05"
    public function index_cal($month)
    {
        //   条件是:   批次有出栏??

        //  TODO:  功能完成后, 需要改为: 查询完成上月的数据后,记录存到表 xxx, 并锁定数据不可修改
        //  TODO:  查询条件:  月份,当月可修改;  (可能还会按厂区拆分)
        //   @以全部库求汇总, 再处理数组  #$storem1 = new \app\admin\model\Storem1 ;
        //  计算月份      *
        //   todo   需要改成可选月份
        //   查询本月数据是随动的,单条记录不可修改.  查询上月数据是已存到表 sta_material 的, 查询当月是即时的
        //   todo    $newdate  需要做成用户选择

        // 月底
        #$month_end = date("Y-m-d", strtotime("+1 month", strtotime($month) )  );
        $month_end = strtotime("+1 month", strtotime($month) );
        $last_month = strtotime("-1 month", strtotime($month) );
        $month_start = strtotime($month);
        // 汇总月份
        $this->view->assign("mark", date("ym", strtotime($month) )."月" );

        $factoryList = Factory::where("status",1)->field("id, name")->select();

        # 汇总月的实时数据:
        $storepro_cur = [];
        foreach($factoryList as &$factory) {
            # 求和查询
//            $list = Db::name("storepro")->alias("sp")
//                ->field("id as sp_id, factory_id, storea_id, field2, field2 as pinxi_id, cal,
//                 sum(cal_num) as stock_num, price, sum(cal_total) as sum_total,
//                 from_unixtime(createtime, '%Y-%m') as createtime")
//                //->where("DATE_FORMAT(sp.createtime,'%Y-%m')='{$month}' ")
//                ->where("createtime", "between",  [$month_start, $month_end] )
//                ->where("deletetime is null")
//                ->where("status", "in", ["normal","hidden","locked"] )
//                ->where("factory_id", $factory['id'])
//                ->group("sp.storea_id, sp.field2, sp.cal")
//                ->select();
//            $storepro_sum = array_merge($storepro_sum, $list);

            #  只查新增, 对比已有的汇总数据
            $list = Db::name("storepro")->alias("sp")
                ->field("id as sp_id, factory_id, storea_id, field2, field2 as pinxi_id,
                 from_unixtime(createtime, '%Y-%m') as createtime")
                // ->field( cal, sum(cal_num) as stock_num, price, sum(cal_total) as sum_total )
                //->where("DATE_FORMAT(sp.createtime,'%Y-%m')='{$month}' ")
                ->where("createtime", "between",  [$month_start, $month_end] )
                ->where("deletetime is null")
                ->where("status", "in", ["normal","hidden","locked"] )
                ->where("factory_id", $factory['id'])
                ->group("sp.storea_id, sp.field2")
                ->select();
            //sqq();
            $storepro_cur = array_merge($storepro_cur, $list);
        }
//print_r($storepro_cur);die;
        // TODO  一定获取上次或上月的数据, 如果无, 则为0, 如果没有批次, 则不显示

        $current_sta = [];
        foreach($factoryList as &$val){
            $fc = Db::name("sta_bios")
                ->field("*, from_unixtime(createtime, '%Y-%m') as createtime ")
                ->where("factory_id", $val['id'])
                ->where("createtime", "between",  [$month_start, $month_end] )
                ->select();
            $current_sta = array_merge($current_sta, $fc);
        }
//print_r($current_sta);die;
                # 上月数据直接结转, 复制基础字段
                #####    改用定时任务结转

        # 实时数据+汇总数据 (不存在则新增 )
        #$this_month_all = $current_sta ;
        $rex = [];
        foreach($storepro_cur as $k => &$val){
            // 获取上月结算数据,  可能存在无的情况
            $last_data = Db::name("sta_bios")
                ->where([
                    'factory_id'=> $val['factory_id'],
                    'storea_id'=> $val['storea_id'],
                    'pinxi_id'=> $val['pinxi_id'],
                    'createtime'=> strtotime($val['createtime']),
                ])->find();
            if(!$last_data){
                // 若新增的品系,   则新建一条数据,  期初数量为: (todo)
                $newData = $val;
                $newData['a1'] = 0;
                $newData['a2'] = 0;     // todo  新增品系的单价??
                $newData['a3'] = 0;     // todo

                if("采购入库"==$val['cal']){
                    $newData['b1'] = $val['stock_num'];
                    $newData['b2'] = $val['price'];
                    $newData['b3'] = $val['sum_total'];
                }
                if("生产入库"==$val['cal']){
                    $newData['c1'] = $val['stock_num'];
                    $newData['c2'] = $val['price'];
                    $newData['c3'] = $val['sum_total'];
                }
                if("调拨入库"==$val['cal']){
                    $newData['d1'] = $val['stock_num'];
                    $newData['d2'] = $val['price'];
                    $newData['d3'] = $val['sum_total'];
                }
                if("调拨出库"==$val['cal']){
                    $newData['e1'] = $val['stock_num'];
                    $newData['e2'] = $val['price'];
                    $newData['e3'] = $val['sum_total'];
                }
                if("死亡出库"==$val['cal']){
                    $newData['f1'] = $val['stock_num'];
                    $newData['f2'] = $val['price'];
                    $newData['f3'] = $val['sum_total'];
                }
                if("淘汰出库"==$val['cal']){
                    $newData['g1'] = $val['stock_num'];
                    $newData['g2'] = $val['price'];
                    $newData['g3'] = $val['sum_total'];
                }
                if("销售出库"==$val['cal']){
                    $newData['h1'] = $val['stock_num'];
                    $newData['h2'] = $val['price'];
                    $newData['h3'] = $val['sum_total'];
                }
                #array_push($this_month_all, $val);
            }
            else
            {
                $newData = $last_data;
                // 若不是新增品系,  则累加到该数据上
                if("采购入库"==$val['cal']){
                    $newData['b1'] += $val['stock_num'];
                    $newData['b3'] += $val['sum_total'];
                    $newData['b2'] = 0;
                }
                if("生产入库"==$val['cal']){
                    $newData['c1'] += $val['stock_num'];
                    $newData['c3'] += $val['sum_total'];
                    $newData['c2'] = 0;
                }
                if("调拨入库"==$val['cal']){
                    $newData['d1'] += $val['stock_num'];
                    $newData['d3'] += $val['sum_total'];
                    $newData['d2'] = 0;
                }
                if("调拨出库"==$val['cal']){
                    $newData['e1'] += $val['stock_num'];
                    $newData['e3'] += $val['sum_total'];
                    $newData['e2'] = 0;
                }
                if("死亡出库"==$val['cal']){
                    $newData['f1'] += $val['stock_num'];
                    $newData['f3'] += $val['sum_total'];
                    $newData['f2'] = 0;
                }
                if("淘汰出库"==$val['cal']){
                    $newData['g1'] += $val['stock_num'];
                    $newData['g3'] += $val['sum_total'];
                    $newData['g2'] = 0;
                }
                if("销售出库"==$val['cal']){
                    $newData['h1'] += $val['stock_num'];
                    $newData['h3'] += $val['sum_total'];
                    $newData['h2'] = 0;
                }

            }

            array_push($rex, $newData);
//            $rex[] = $newData ;
//            print_r($newData); die;

//            die;
//            $val['a1'] = $last_data ? $last_data['i1'] : 0 ;                 //  a1  '月初存栏',
//            $val['a2'] = $last_data ? $last_data['i2'] : 0 ;                 //  a2  '单价',
//            $val['a3'] = $last_data ? $last_data['i3'] : 0 ;                 //  a3  '金额',
//            $val['createtime'] = $month_start ;
            #$val['last_model'] = $last_data ;
        }
        # 合并后的行数据: $this_month_all: 只有行, 期初数量,

        foreach($rex as &$val){
            $val['createtime'] = strtotime($val['createtime']);
        }
//print_r($rex); die;
        $stabiosModel = new \app\admin\model\sta\Bios ;
        $result_of_calmonth = $stabiosModel->allowField(true)->saveAll($rex) ;

print_r($result_of_calmonth);die;

        $set = $this_month_all;
        # 计算实时数据
        foreach($set as $k => $val){
            if(1) {
                ###  第二版汇总表, 只计 采购入库 等汇总
                # 采购入库  生产入库  调拨入库  调拨出库  死亡出库  淘汰出库  销售出库  月末结余
                #
                $aa = Db::name("storepro")->alias("sp")
                    ->field("id, factory_id, storea_id, field2, sum(num) as sum_num, cal_num, cal  , sum(total) as sum_total, sum(price) as every_price ")
                    ->where("createtime", "between", [$month_start, $month_end])
                    ->where("deletetime is null")
                    ->where("status", "in", ["normal","hidden","locked"] )
                    ->where("field2", $val['field2'])
                    ->where("storea_id", $val['storea_id'])
                    ->where("cal", "=", "采购入库")
                    ->select();
                //->where("cal_num", "lt", 0 )
                //->group("cal")
//                sqq(); die;
                $bb = Db::name("storepro")->alias("sp")
                    ->field("id, factory_id, storea_id, field2, sum(num) as sum_num, cal_num, cal  , sum(total) as sum_total, sum(price) as every_price ")
                    ->where("createtime", "between", [$month_start, $month_end])
                    ->where("deletetime is null")
                    ->where("status", "in", ["normal","hidden","locked"] )
                    ->where("field2", $val['field2'])
                    ->where("storea_id", $val['storea_id'])
                    ->where("cal", "=", "生产入库")
                    ->select();
                $cc = Db::name("storepro")->alias("sp")
                    ->field("id, factory_id, storea_id, field2, sum(num) as sum_num, cal_num, cal  , sum(total) as sum_total, sum(price) as every_price ")
                    ->where("createtime", "between", [$month_start, $month_end])
                    ->where("deletetime is null")
                    ->where("status", "in", ["normal","hidden","locked"] )
                    ->where("field2", $val['field2'])
                    ->where("storea_id", $val['storea_id'])
                    ->where("cal", "=", "调拨入库")
                    ->select();
                $dd = Db::name("storepro")->alias("sp")
                    ->field("id, factory_id, storea_id, field2, sum(num) as sum_num, cal_num, cal  , sum(total) as sum_total, sum(price) as every_price ")
                    ->where("createtime", "between", [$month_start, $month_end])
                    ->where("deletetime is null")
                    ->where("status", "in", ["normal","hidden","locked"] )
                    ->where("field2", $val['field2'])
                    ->where("storea_id", $val['storea_id'])
                    ->where("cal", "=", "调拨出库")
                    ->select();
                $ee = Db::name("storepro")->alias("sp")
                    ->field("id, factory_id, storea_id, field2, sum(num) as sum_num, cal_num, cal  , sum(total) as sum_total, sum(price) as every_price ")
                    ->where("createtime", "between", [$month_start, $month_end])
                    ->where("deletetime is null")
                    ->where("status", "in", ["normal","hidden","locked"] )
                    ->where("field2", $val['field2'])
                    ->where("storea_id", $val['storea_id'])
                    ->where("cal", "=", "死亡出库")
                    ->select();
                $ff = Db::name("storepro")->alias("sp")
                    ->field("id, factory_id, storea_id, field2, sum(num) as sum_num, cal_num, cal  , sum(total) as sum_total, sum(price) as every_price ")
                    ->where("createtime", "between", [$month_start, $month_end])
                    ->where("deletetime is null")
                    ->where("status", "in", ["normal","hidden","locked"] )
                    ->where("field2", $val['field2'])
                    ->where("storea_id", $val['storea_id'])
                    ->where("cal", "=", "淘汰出库")
                    ->select();
                $gg = Db::name("storepro")->alias("sp")
                    ->field("id, factory_id, storea_id, field2, sum(num) as sum_num, cal_num, cal  , sum(total) as sum_total, sum(price) as every_price ")
                    ->where("createtime", "between", [$month_start, $month_end])
                    ->where("deletetime is null")
                    ->where("status", "in", ["normal","hidden","locked"] )
                    ->where("field2", $val['field2'])
                    ->where("storea_id", $val['storea_id'])
                    ->where("cal", "=", "销售出库")
                    ->select();
            }
//print_r($aa); die;
//            echo Db::getLastSql();die;

            # 采购入库  生产入库  调拨入库  调拨出库  死亡出库  淘汰出库  销售出库  月末结余
            $set[$k]['b1'] = $aa ? $aa[0]['sum_num'] : 0;
            $set[$k]['b2'] = $aa ? $aa[0]['every_price'] : 0;
            $set[$k]['b3'] = $aa ? $aa[0]['sum_total'] : 0;
            $set[$k]['b4'] = 0;
            $set[$k]['b5'] = 0;

            $set[$k]['c1'] = $bb ? $bb[0]['sum_num'] : 0;
            $set[$k]['c2'] = $bb ? $bb[0]['every_price'] : 0;
            $set[$k]['c3'] = $bb ? $bb[0]['sum_total'] : 0;

            $set[$k]['d1'] = $cc ? $cc[0]['sum_num'] : 0;
            $set[$k]['d2'] = $cc ? $cc[0]['every_price'] : 0;
            $set[$k]['d3'] = $cc ? $cc[0]['sum_total'] : 0;

            $set[$k]['e1'] = $dd ? $dd[0]['sum_num'] : 0;
            $set[$k]['e2'] = $dd ? $dd[0]['every_price'] : 0;
            $set[$k]['e3'] = $dd ? $dd[0]['sum_total'] : 0;

            $set[$k]['f1'] = $ee ? $ee[0]['sum_num'] : 0;
            $set[$k]['f2'] = $ee ? $ee[0]['every_price'] : 0;
            $set[$k]['f3'] = $ee ? $ee[0]['sum_total'] : 0;

            $set[$k]['g1'] = $ff ? $ff[0]['sum_num'] : 0;
            $set[$k]['g2'] = $ff ? $ff[0]['every_price'] : 0;
            $set[$k]['g3'] = $ff ? $ff[0]['sum_total'] : 0;

            $set[$k]['h1'] = $gg ? $gg[0]['sum_num'] : 0;
            $set[$k]['h2'] = $gg ? $gg[0]['every_price'] : 0;
            $set[$k]['h3'] = $gg ? $gg[0]['sum_total'] : 0;

            # 月末
            $set[$k]['i1'] = intval($set[$k]['a1']) + intval($set[$k]['b1']) + intval($set[$k]['c1']) + intval($set[$k]['d1'])
                - intval($set[$k]['e1']) - intval($set[$k]['f1']) + intval($set[$k]['g1']) + intval($set[$k]['h1']);

            $set[$k]['i3'] = intval($set[$k]['a3']) + intval($set[$k]['b3']) + intval($set[$k]['c3']) + intval($set[$k]['d3'])
                - intval($set[$k]['e3']) - intval($set[$k]['f3']) + intval($set[$k]['g3']) + intval($set[$k]['h3']);

            $set[$k]['i2'] = my_divide($set[$k]['i3'], $set[$k]['i1'], 8);


            $set[$k]['createtime'] = strtotime($month) ;
            $set[$k]['status'] = $month_end < time() ? "locked" : "normal" ;
        }
//print_r($set);die;
        ## 保存到汇总表
        if( empty($set) ){
            return 0;
        }
//exit;
        // todo  如果显示了当月数据, 重新生成需要删除当月的临时数据.
        $stabiosModel = new \app\admin\model\sta\Bios ;
        $res_all = $stabiosModel->allowField(true)->saveAll($set) ;

        return ['data'=> $set, 'lines'=> count($res_all) ];
    }

    /**
     * 统计
     *
     * @return string
     * @throws \think\Exception
     */
    public function summonth_v0()
    {
        if (false === $this->request->isPost()) {
            return $this->view->fetch();
        }
        $params = $this->request->post('row/a');
        if (empty($params)) {
            $this->error(__('Parameter %s can not be empty', ''));
        }
        $params = $this->preExcludeFields($params);

        // todo 增加强制更新, 或者允许删除后, 重新生成

        // todo 如果存在上月数据, 或更早的数据, 结算之前的数据, 创建这个月的期初数据


        // 统计月份
        $exist = Db::name("sta_bios")
            ->where("createtime", strtotime($params['createtime']) )
            ->where("status", "locked" )
            ->select();
        if( count($exist) ){
            $result = false;
        }else{
            //  是否需要校验
            $cal_month = $params['createtime'];
            //  计算
            $result = $this->index_cal($cal_month);
        }

        if ($result === false) {
            $this->error(__('汇总数据已存在,无法覆盖已存在的数据'));
        }
        if ($result['lines'] == 0) {
            $this->error(__('未获取到有效数据! '));
        }
        $this->success(__('汇总数据生成完毕.'). " 更新行数: ". json_encode($result['lines'])  );
    }

    // 计算x月的汇总数据    格式 "2022-05"
    public function index_cal_v0($month)
    {
        //   条件是:   批次有出栏??

        //  TODO:  功能完成后, 需要改为: 查询完成上月的数据后,记录存到表 xxx, 并锁定数据不可修改
        //  TODO:  查询条件:  月份,当月可修改;  (可能还会按厂区拆分)
        //   @以全部库求汇总, 再处理数组  #$storem1 = new \app\admin\model\Storem1 ;
        //  计算月份      *
        //   todo   需要改成可选月份
        //   查询本月数据是随动的,单条记录不可修改.  查询上月数据是已存到表 sta_material 的, 查询当月是即时的
        //   todo    $newdate  需要做成用户选择

        // 月底
        #$month_end = date("Y-m-d", strtotime("+1 month", strtotime($month) )  );
        $month_end = strtotime("+1 month", strtotime($month) );
        $last_month = strtotime("-1 month", strtotime($month) );
        $month_start = strtotime($month);
        // 汇总月份
        $this->view->assign("mark", date("ym", strtotime($month) )."月" );

        $factoryList = Factory::where("status",1)->field("id, name")->select();

        # 汇总月的实时数据:
        $storepro_sum = [];
        foreach($factoryList as &$factory) {
            $list = Db::name("storepro")->alias("sp")
                ->field("id as sp_id, factory_id, storea_id, field2, field2 as pinxi_id, sum(cal_num) as stock_num ")
                //->where("DATE_FORMAT(sp.createtime,'%Y-%m')='{$month}' ")
                ->where("createtime", "between",  [$month_start, $month_end] )
                ->where("deletetime is null")
                ->where("status", "in", ["normal","hidden","locked"] )
                ->where("factory_id", $factory['id'])
                ->group("sp.storea_id, sp.field2")
                ->select();
            //sqq();
            $storepro_sum = array_merge($storepro_sum, $list);
        }
//print_r($storepro_sum);die;
        // TODO  一定获取上次或上月的数据, 如果无, 则为0, 如果没有批次, 则不显示

        $sta_lastm = [];
        foreach($factoryList as &$val){
            $fc = Db::name("sta_bios")->field("*")
                ->where("factory_id", $val['id'])
                //->where("storea_id", $val['storea_id'])
                //->where("pinxi_id", $val['field2'])
                //->where("DATE_FORMAT(createtime,'%Y-%m-%d')='{$lastmonth}' ")
                ->where("createtime", "between",  [$last_month, $month_start] )
                ->select();
            //sqq();
            $sta_lastm = array_merge($sta_lastm, $fc);
        }
//print_r($sta_lastm);die;

        # 上月数据直接结转, 复制基础字段
        $this_month_tmp = [] ;
        foreach($sta_lastm as $cur ){
            $new = [
                "createtime" => $month_start,
                "factory_id" => $cur['factory_id'],
                "storesub_id" => $cur['storesub_id'],
                "storea_id" => $cur['storea_id'],
                "gene_id" => $cur['gene_id'],
                "pinxi_id" => $cur['pinxi_id'],
                "storem1_id" => $cur['storem1_id'],
                "a1" => $cur['i1'],     // 上月期末数据
                "a2" => $cur['i2'],
                "a3" => $cur['i3']
            ];
            array_push($this_month_tmp, $new);
        }
//print_r($this_month_tmp);die;
        # 合并期初数据, 新增数据 : 只要行字段
        $this_month_all = $this_month_tmp;
        foreach($storepro_sum as $k => &$val){
            // 获取上月结算数据,  可能存在无的情况
            $last_data = Db::name("sta_bios")
                ->where([
                    'factory_id'=> $val['factory_id'],
                    'storea_id'=> $val['storea_id'],
                    'pinxi_id'=> $val['pinxi_id'],
                    'createtime'=> $last_month,
                ])->find();

            $val['a1'] = $last_data ? $last_data['i1'] : 0 ;                 //  a1  '月初存栏',
            $val['a2'] = $last_data ? $last_data['i2'] : 0 ;                 //  a2  '单价',
            $val['a3'] = $last_data ? $last_data['i3'] : 0 ;                 //  a3  '金额',
            $val['createtime'] = $month_start ;
            #$val['last_model'] = $last_data ;

            if(!$last_data){
                array_push($this_month_all, $val);
            }
        }
        # 合并后的行数据: $this_month_all: 只有行, 期初数量,
//print_r($this_month_all); die;

        unset($set);
        $set = $this_month_all;

        # 计算实时数据
        foreach($set as $k => $val){
            if(1) {
                ###  第二版汇总表, 只计 采购入库 等汇总
                # 采购入库  生产入库  调拨入库  调拨出库  死亡出库  淘汰出库  销售出库  月末结余
                #
                $aa = Db::name("storepro")->alias("sp")
                    ->field("id, factory_id, storea_id, field2, sum(num) as sum_num, cal_num, cal  , sum(total) as sum_total, sum(price) as every_price ")
                    ->where("createtime", "between", [$month_start, $month_end])
                    ->where("deletetime is null")
                    ->where("status", "in", ["normal","hidden","locked"] )
                    ->where("field2", $val['field2'])
                    ->where("storea_id", $val['storea_id'])
                    ->where("cal", "=", "采购入库")
                    ->select();
                    //->where("cal_num", "lt", 0 )
                    //->group("cal")
//                sqq(); die;
                $bb = Db::name("storepro")->alias("sp")
                    ->field("id, factory_id, storea_id, field2, sum(num) as sum_num, cal_num, cal  , sum(total) as sum_total, sum(price) as every_price ")
                    ->where("createtime", "between", [$month_start, $month_end])
                    ->where("deletetime is null")
                    ->where("status", "in", ["normal","hidden","locked"] )
                    ->where("field2", $val['field2'])
                    ->where("storea_id", $val['storea_id'])
                    ->where("cal", "=", "生产入库")
                    ->select();
                $cc = Db::name("storepro")->alias("sp")
                    ->field("id, factory_id, storea_id, field2, sum(num) as sum_num, cal_num, cal  , sum(total) as sum_total, sum(price) as every_price ")
                    ->where("createtime", "between", [$month_start, $month_end])
                    ->where("deletetime is null")
                    ->where("status", "in", ["normal","hidden","locked"] )
                    ->where("field2", $val['field2'])
                    ->where("storea_id", $val['storea_id'])
                    ->where("cal", "=", "调拨入库")
                    ->select();
                $dd = Db::name("storepro")->alias("sp")
                    ->field("id, factory_id, storea_id, field2, sum(num) as sum_num, cal_num, cal  , sum(total) as sum_total, sum(price) as every_price ")
                    ->where("createtime", "between", [$month_start, $month_end])
                    ->where("deletetime is null")
                    ->where("status", "in", ["normal","hidden","locked"] )
                    ->where("field2", $val['field2'])
                    ->where("storea_id", $val['storea_id'])
                    ->where("cal", "=", "调拨出库")
                    ->select();
                $ee = Db::name("storepro")->alias("sp")
                    ->field("id, factory_id, storea_id, field2, sum(num) as sum_num, cal_num, cal  , sum(total) as sum_total, sum(price) as every_price ")
                    ->where("createtime", "between", [$month_start, $month_end])
                    ->where("deletetime is null")
                    ->where("status", "in", ["normal","hidden","locked"] )
                    ->where("field2", $val['field2'])
                    ->where("storea_id", $val['storea_id'])
                    ->where("cal", "=", "死亡出库")
                    ->select();
                $ff = Db::name("storepro")->alias("sp")
                    ->field("id, factory_id, storea_id, field2, sum(num) as sum_num, cal_num, cal  , sum(total) as sum_total, sum(price) as every_price ")
                    ->where("createtime", "between", [$month_start, $month_end])
                    ->where("deletetime is null")
                    ->where("status", "in", ["normal","hidden","locked"] )
                    ->where("field2", $val['field2'])
                    ->where("storea_id", $val['storea_id'])
                    ->where("cal", "=", "淘汰出库")
                    ->select();
                $gg = Db::name("storepro")->alias("sp")
                    ->field("id, factory_id, storea_id, field2, sum(num) as sum_num, cal_num, cal  , sum(total) as sum_total, sum(price) as every_price ")
                    ->where("createtime", "between", [$month_start, $month_end])
                    ->where("deletetime is null")
                    ->where("status", "in", ["normal","hidden","locked"] )
                    ->where("field2", $val['field2'])
                    ->where("storea_id", $val['storea_id'])
                    ->where("cal", "=", "销售出库")
                    ->select();
            }
//print_r($aa); die;
//            echo Db::getLastSql();die;

            # 采购入库  生产入库  调拨入库  调拨出库  死亡出库  淘汰出库  销售出库  月末结余
            $set[$k]['b1'] = $aa ? $aa[0]['sum_num'] : 0;
            $set[$k]['b2'] = $aa ? $aa[0]['every_price'] : 0;
            $set[$k]['b3'] = $aa ? $aa[0]['sum_total'] : 0;
            $set[$k]['b4'] = 0;
            $set[$k]['b5'] = 0;

            $set[$k]['c1'] = $bb ? $bb[0]['sum_num'] : 0;
            $set[$k]['c2'] = $bb ? $bb[0]['every_price'] : 0;
            $set[$k]['c3'] = $bb ? $bb[0]['sum_total'] : 0;

            $set[$k]['d1'] = $cc ? $cc[0]['sum_num'] : 0;
            $set[$k]['d2'] = $cc ? $cc[0]['every_price'] : 0;
            $set[$k]['d3'] = $cc ? $cc[0]['sum_total'] : 0;

            $set[$k]['e1'] = $dd ? $dd[0]['sum_num'] : 0;
            $set[$k]['e2'] = $dd ? $dd[0]['every_price'] : 0;
            $set[$k]['e3'] = $dd ? $dd[0]['sum_total'] : 0;

            $set[$k]['f1'] = $ee ? $ee[0]['sum_num'] : 0;
            $set[$k]['f2'] = $ee ? $ee[0]['every_price'] : 0;
            $set[$k]['f3'] = $ee ? $ee[0]['sum_total'] : 0;

            $set[$k]['g1'] = $ff ? $ff[0]['sum_num'] : 0;
            $set[$k]['g2'] = $ff ? $ff[0]['every_price'] : 0;
            $set[$k]['g3'] = $ff ? $ff[0]['sum_total'] : 0;

            $set[$k]['h1'] = $gg ? $gg[0]['sum_num'] : 0;
            $set[$k]['h2'] = $gg ? $gg[0]['every_price'] : 0;
            $set[$k]['h3'] = $gg ? $gg[0]['sum_total'] : 0;

            # 月末
            $set[$k]['i1'] = intval($set[$k]['a1']) + intval($set[$k]['b1']) + intval($set[$k]['c1']) + intval($set[$k]['d1'])
                            - intval($set[$k]['e1']) - intval($set[$k]['f1']) + intval($set[$k]['g1']) + intval($set[$k]['h1']);

            $set[$k]['i3'] = intval($set[$k]['a3']) + intval($set[$k]['b3']) + intval($set[$k]['c3']) + intval($set[$k]['d3'])
                            - intval($set[$k]['e3']) - intval($set[$k]['f3']) + intval($set[$k]['g3']) + intval($set[$k]['h3']);

            $set[$k]['i2'] = my_divide($set[$k]['i3'], $set[$k]['i1'], 8);


            $set[$k]['createtime'] = strtotime($month) ;
            $set[$k]['status'] = $month_end < time() ? "locked" : "normal" ;
        }
//print_r($set);die;
        ## 保存到汇总表
        if( empty($set) ){
            return 0;
        }
//exit;
        // todo  如果显示了当月数据, 重新生成需要删除当月的临时数据.
        $stabiosModel = new \app\admin\model\sta\Bios ;
        $res_all = $stabiosModel->allowField(true)->saveAll($set) ;

        return ['data'=> $set, 'lines'=> count($res_all) ];
    }

    private function get_last_sta($va, $list){
//        foreach($list as )
    }



    /**
     * 回收站
     *
     * @return string|Json
     * @throws \think\Exception
     */
    public function recyclebin()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if (false === $this->request->isAjax()) {
            return $this->view->fetch();
        }
        [$where, $sort, $order, $offset, $limit] = $this->buildparams();
        $list = $this->model
            ->onlyTrashed()
            ->where($where)
            ->order($sort, $order)
            ->paginate($limit);
        $result = ['total' => $list->total(), 'rows' => $list->items()];
        return json($result);
    }

    /**
     * 添加
     *
     * @return string
     * @throws \think\Exception
     */
    public function add()
    {
        if (false === $this->request->isPost()) {
            return $this->view->fetch();
        }
        $params = $this->request->post('row/a');
        if (empty($params)) {
            $this->error(__('Parameter %s can not be empty', ''));
        }
        $params = $this->preExcludeFields($params);
        $params['createtime'] = is_numeric($params['createtime']) ? $params['createtime'] : strtotime($params['createtime']);

        if ($this->dataLimit && $this->dataLimitFieldAutoFill) {
            $params[$this->dataLimitField] = $this->auth->id;
        }
        $result = false;
        Db::startTrans();
        try {
            //是否采用模型验证
            if ($this->modelValidate) {
                $name = str_replace("\\model\\", "\\validate\\", get_class($this->model));
                $validate = is_bool($this->modelValidate) ? ($this->modelSceneValidate ? $name . '.add' : $name) : $this->modelValidate;
                $this->model->validateFailException()->validate($validate);
            }
            $result = $this->model->allowField(true)->save($params);
            Db::commit();
        } catch (ValidateException|PDOException|Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
        if ($result === false) {
            $this->error(__('No rows were inserted'));
        }
        $this->success();
    }

    /**
     * 编辑
     *
     * @param $ids
     * @return string
     * @throws DbException
     * @throws \think\Exception
     */
    public function edit($ids = null)
    {
        $row = $this->model->get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }
        $adminIds = $this->getDataLimitAdminIds();
        if (is_array($adminIds) && !in_array($row[$this->dataLimitField], $adminIds)) {
            $this->error(__('You have no permission'));
        }
        if (false === $this->request->isPost()) {
            $this->view->assign('row', $row);
            return $this->view->fetch();
        }
        $params = $this->request->post('row/a');
        if (empty($params)) {
            $this->error(__('Parameter %s can not be empty', ''));
        }
        $params = $this->preExcludeFields($params);
        $params['createtime'] = is_numeric($params['createtime']) ? $params['createtime'] : strtotime($params['createtime']);

        $result = false;
        Db::startTrans();
        try {
            //是否采用模型验证
            if ($this->modelValidate) {
                $name = str_replace("\\model\\", "\\validate\\", get_class($this->model));
                $validate = is_bool($this->modelValidate) ? ($this->modelSceneValidate ? $name . '.edit' : $name) : $this->modelValidate;
                $row->validateFailException()->validate($validate);
            }
            $result = $row->allowField(true)->save($params);
            Db::commit();
        } catch (ValidateException|PDOException|Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
        if (false === $result) {
            $this->error(__('No rows were updated'));
        }
        $this->success();
    }

    /**
     * 删除
     *
     * @param $ids
     * @return void
     * @throws DbException
     * @throws DataNotFoundException
     * @throws ModelNotFoundException
     */
    public function del($ids = null)
    {
        if (false === $this->request->isPost()) {
            $this->error(__("Invalid parameters"));
        }
        $ids = $ids ?: $this->request->post("ids");
        if (empty($ids)) {
            $this->error(__('Parameter %s can not be empty', 'ids'));
        }
        $pk = $this->model->getPk();
        $adminIds = $this->getDataLimitAdminIds();
        if (is_array($adminIds)) {
            $this->model->where($this->dataLimitField, 'in', $adminIds);
        }
        $list = $this->model->where($pk, 'in', $ids)->select();

        $count = 0;
        Db::startTrans();
        try {
            foreach ($list as $item) {
                $count += $item->delete();
            }
            Db::commit();
        } catch (PDOException|Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
        if ($count) {
            $this->success();
        }
        $this->error(__('No rows were deleted'));
    }

    /**
     * 真实删除
     *
     * @param $ids
     * @return void
     */
    public function destroy($ids = null)
    {
        if (false === $this->request->isPost()) {
            $this->error(__("Invalid parameters"));
        }
        $ids = $ids ?: $this->request->post('ids');
        if (empty($ids)) {
            $this->error(__('Parameter %s can not be empty', 'ids'));
        }
        $pk = $this->model->getPk();
        $adminIds = $this->getDataLimitAdminIds();
        if (is_array($adminIds)) {
            $this->model->where($this->dataLimitField, 'in', $adminIds);
        }
        $this->model->where($pk, 'in', $ids);
        $count = 0;
        Db::startTrans();
        try {
            $list = $this->model->onlyTrashed()->select();
            foreach ($list as $item) {
                $count += $item->delete(true);
            }
            Db::commit();
        } catch (PDOException|Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
        if ($count) {
            $this->success();
        }
        $this->error(__('No rows were deleted'));
    }

    /**
     * 还原
     *
     * @param $ids
     * @return void
     */
    public function restore($ids = null)
    {
        if (false === $this->request->isPost()) {
            $this->error(__('Invalid parameters'));
        }
        $ids = $ids ?: $this->request->post('ids');
        $pk = $this->model->getPk();
        $adminIds = $this->getDataLimitAdminIds();
        if (is_array($adminIds)) {
            $this->model->where($this->dataLimitField, 'in', $adminIds);
        }
        if ($ids) {
            $this->model->where($pk, 'in', $ids);
        }
        $count = 0;
        Db::startTrans();
        try {
            $list = $this->model->onlyTrashed()->select();
            foreach ($list as $item) {
                $count += $item->restore();
            }
            Db::commit();
        } catch (PDOException|Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
        if ($count) {
            $this->success();
        }
        $this->error(__('No rows were updated'));
    }

    /**
     * 批量更新
     *
     * @param $ids
     * @return void
     */
    public function multi($ids = null)
    {
        if (false === $this->request->isPost()) {
            $this->error(__('Invalid parameters'));
        }
        $ids = $ids ?: $this->request->post('ids');
        if (empty($ids)) {
            $this->error(__('Parameter %s can not be empty', 'ids'));
        }

        if (false === $this->request->has('params')) {
            $this->error(__('No rows were updated'));
        }
        parse_str($this->request->post('params'), $values);
        $values = $this->auth->isSuperAdmin() ? $values : array_intersect_key($values, array_flip(is_array($this->multiFields) ? $this->multiFields : explode(',', $this->multiFields)));
        if (empty($values)) {
            $this->error(__('You have no permission'));
        }
        $adminIds = $this->getDataLimitAdminIds();
        if (is_array($adminIds)) {
            $this->model->where($this->dataLimitField, 'in', $adminIds);
        }
        $count = 0;
        Db::startTrans();
        try {
            $list = $this->model->where($this->model->getPk(), 'in', $ids)->select();
            foreach ($list as $item) {
                $count += $item->allowField(true)->isUpdate(true)->save($values);
            }
            Db::commit();
        } catch (PDOException|Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
        if ($count) {
            $this->success();
        }
        $this->error(__('No rows were updated'));
    }

    /**
     * 导入
     *
     * @return void
     * @throws PDOException
     * @throws BindParamException
     */
    protected function import()
    {
        $file = $this->request->request('file');
        if (!$file) {
            $this->error(__('Parameter %s can not be empty', 'file'));
        }
        $filePath = ROOT_PATH . DS . 'public' . DS . $file;
        if (!is_file($filePath)) {
            $this->error(__('No results were found'));
        }
        //实例化reader
        $ext = pathinfo($filePath, PATHINFO_EXTENSION);
        if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
            $this->error(__('Unknown data format'));
        }
        if ($ext === 'csv') {
            $file = fopen($filePath, 'r');
            $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
            $fp = fopen($filePath, 'w');
            $n = 0;
            while ($line = fgets($file)) {
                $line = rtrim($line, "\n\r\0");
                $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                if ($encoding !== 'utf-8') {
                    $line = mb_convert_encoding($line, 'utf-8', $encoding);
                }
                if ($n == 0 || preg_match('/^".*"$/', $line)) {
                    fwrite($fp, $line . "\n");
                } else {
                    fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
                }
                $n++;
            }
            fclose($file) || fclose($fp);

            $reader = new Csv();
        } elseif ($ext === 'xls') {
            $reader = new Xls();
        } else {
            $reader = new Xlsx();
        }

        //导入文件首行类型,默认是注释,如果需要使用字段名称请使用name
        $importHeadType = isset($this->importHeadType) ? $this->importHeadType : 'comment';

        $table = $this->model->getQuery()->getTable();
        $database = \think\Config::get('database.database');
        $fieldArr = [];
        $list = db()->query("SELECT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?", [$table, $database]);
        foreach ($list as $k => $v) {
            if ($importHeadType == 'comment') {
                $fieldArr[$v['COLUMN_COMMENT']] = $v['COLUMN_NAME'];
            } else {
                $fieldArr[$v['COLUMN_NAME']] = $v['COLUMN_NAME'];
            }
        }

        //加载文件
        $insert = [];
        try {
            if (!$PHPExcel = $reader->load($filePath)) {
                $this->error(__('Unknown data format'));
            }
            $currentSheet = $PHPExcel->getSheet(0);  //读取文件中的第一个工作表
            $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
            $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
            $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
            $fields = [];
            for ($currentRow = 1; $currentRow <= 1; $currentRow++) {
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $fields[] = $val;
                }
            }

            for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
                $values = [];
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $values[] = is_null($val) ? '' : $val;
                }
                $row = [];
                $temp = array_combine($fields, $values);
                foreach ($temp as $k => $v) {
                    if (isset($fieldArr[$k]) && $k !== '') {
                        $row[$fieldArr[$k]] = $v;
                    }
                }
                if ($row) {
                    $insert[] = $row;
                }
            }
        } catch (Exception $exception) {
            $this->error($exception->getMessage());
        }
        if (!$insert) {
            $this->error(__('No rows were updated'));
        }

        try {
            //是否包含admin_id字段
            $has_admin_id = false;
            foreach ($fieldArr as $name => $key) {
                if ($key == 'admin_id') {
                    $has_admin_id = true;
                    break;
                }
            }
            if ($has_admin_id) {
                $auth = Auth::instance();
                foreach ($insert as &$val) {
                    if (!isset($val['admin_id']) || empty($val['admin_id'])) {
                        $val['admin_id'] = $auth->isLogin() ? $auth->id : 0;
                    }
                }
            }
            $this->model->saveAll($insert);
        } catch (PDOException $exception) {
            $msg = $exception->getMessage();
            if (preg_match("/.+Integrity constraint violation: 1062 Duplicate entry '(.+)' for key '(.+)'/is", $msg, $matches)) {
                $msg = "导入失败，包含【{$matches[1]}】的记录已存在";
            };
            $this->error($msg);
        } catch (Exception $e) {
            $this->error($e->getMessage());
        }

        $this->success();
    }




}
